'''
数据库相关的操作(面试经常会问)
1. 操作数据库用的什么模块  pymysql
2. 连接数据库用的什么方法，有哪些参数 connect 方法，同sqlyog连接数据库需要的参数类似
'''
import pymysql


# 连接数据库
def connect(db):
    '''
    :param db:  字典格式的数据
    db = {"ip": "192.168.124.29", "port": 3306, "user": "root", "pwd": "123456", "dbname": "apple"}
    :return:
    '''
    try:
        conn = pymysql.connect(user=db['user'],
                               password=db['pwd'],
                               host=db['ip'],
                               database=db['dbname'],
                               port=int(db['port']),
                               charset="utf8")  # utf8 没有中划线
        print("连接数据库成功")
        return conn
    except  Exception as e:
        print(f"连接数据库异常，异常信息为：{e}")


# 执行sql
def execute(conn, sql):
    try:
        c = conn.cursor()  # 获取到游标
        c.execute(sql)  # 使用游标执行sql语句，如果是增加、删除，会有一个数量。
        a = c.fetchall()  # 如果是查询的语句，会有表格。
        conn.commit()  # 提交
        print(f"执行语句{sql}成功")
        return a  # 返回查询的结果
    except Exception as e:
        print("执行sql语句异常")


# 断开连接
def close(conn):
    try:
        conn.close()
    except Exception as e:
        print(f"断开连接异常，异常信息为：{e}")

# 根据手机号码删除用户
def delete_user(db, phone):
    conn = connect(db)
    execute(conn, f"delete from member where mobilephone='{phone}'")
    close(conn)

# 脚本中屏蔽了sql语句，脚本中使用起来简单
def get_user(db, phone):
    conn = connect(db)
    a = execute(conn, f"select * from member where mobilephone='{phone}'")
    close(conn)
    return a

def get_amout(db, phone):
    conn = connect(db)
    a = execute(conn, f"select leaveamount from member where mobilephone='{phone}'")
    close(conn)
    return a

if __name__ == '__main__':
    db = {"ip": "192.168.124.29", "port": 3306, "user": "root", "pwd": "123456", "dbname": "apple"}
    a = get_amout(db, "13333442323")
    print(a[0][0])
    print(len(a))
